# постройте тепловую карту Retention Rate
# Чтобы построить тепловую карту Retention Rate, необходим расчёт лайфтайма. Посчитаем его с помощью sql, поправив
# запрос и по новым данным будем строить сводную таблицу.
query = '''
WITH profiles AS
(SELECT DISTINCT DATE_TRUNC('month', creation_date::date)::date AS cohort_dt,
user_id,
COUNT(user_id) OVER(PARTITION BY DATE_TRUNC('month', creation_date::date)) AS cohort_users_cnt
FROM
(SELECT *,
DENSE_RANK() OVER(PARTITION BY user_id ORDER BY creation_date) as rn
FROM stackoverflow.posts
ORDER BY user_id) as subq
WHERE rn = 1),
sessions AS
(SELECT user_id,
DATE_TRUNC('month', creation_date::date)::date AS session_date
FROM stackoverflow.posts
GROUP BY 1, 2)
SELECT p.cohort_dt,
session_date,
COUNT(p.user_id) AS users_cnt,
cohort_users_cnt,
-- далее вместо умножения на 100.0, будет умножение на 1.0, чтобы в хитмэпе получить проценты
ROUND(COUNT(p.user_id) * 1.0 / cohort_users_cnt, 4) AS retention_rate, -- а точность сохраним до 4 знаков
(EXTRACT(MONTH FROM session_date::date) - EXTRACT(MONTH FROM p.cohort_dt::date))::integer AS lifetime
FROM profiles p JOIN sessions s ON p.user_id = s.user_id
GROUP BY 1, 2, 4
ORDER BY 1, 2;
'''
report = pd.read_sql_query(query, con=engine)
# Убедимся, что столбец lifetime получился
report.head(10)
